package com.wuchaooooo.service.zjutsds.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import org.apache.commons.lang3.StringUtils;
import com.wuchaooooo.service.zjutsds.utils.DateUtils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;


/**
 * Created by arthur on 11/10/2020.
 */
@Repository
public class QueryDAO {
	private static final Logger LOGGER = LoggerFactory.getLogger(QueryDAO.class);

	@Resource(name = "secondaryJdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	// /**
	// * 相对于基本专业推荐，取得更多的专业推荐
	// *
	// * @param pCareer
	// * @return
	// */
	// public List<Map<String, Object>> getMajors() {
	// String sql = "SELECT * FROM data_major limit 0, 100";
	// List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
	// return list;
	// }

	/**
	 * 用户登录
	 * 
	 * @param params
	 * @return
	 */
	public Map<String, Object> loginUser(Map<String, Object> params) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT no, name, gender, sel1, sel2, school, score, count");
		sql.append(" FROM data_student");
		sql.append(" WHERE no = :username");
		sql.append("   AND password = :password");
		try {
			NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);

			LOGGER.info("SQL语句：" + sql.toString());
			LOGGER.info("参数为：" + params.toString());
			return nameJdbc.queryForMap(sql.toString(), params);
		} catch (Exception e) {
			LOGGER.error("用户登录失败：" + params.toString());
			LOGGER.error("错误信息:" + e.getMessage());
			return null;
		}
	}

	/**
	 * 录入新用户
	 * 
	 * @param params
	 * @return
	 */
	public int addUser(Map<String, Object> params) {
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO data_student(no, name, gender, password, sel1, sel2, school, city, phoneNo, count, create_by, create_date, update_by, update_date)");
		sql.append("VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?)");

		
		String now = DateUtils.getSimpleDateTime();
		Object[] args = new Object[]{params.get("phoneNo"), params.get("name"), params.get("gender"),
		params.get("password"), params.get("sel1_code"), params.get("sel2_code"),
		params.get("school"), params.get("city"), params.get("phoneNo"), params.get("phoneNo"), now,
		params.get("phoneNo"), now };
		
		LOGGER.info("SQL语句：" + sql.toString());
		LOGGER.info("参数为：" + Arrays.toString(args));
		try {

			return this.jdbcTemplate.update(sql.toString(), args);
		} catch (Exception e) {
			LOGGER.error("参数：" + params.toString());
			LOGGER.error("用户注册失败：：" + e.getMessage());
			return 0;
		}
	}

	/**
	 * 新高考数据检索
	 * 
	 * @param params
	 * @return
	 */
	public List<Map<String, Object>> getQuery(Map<String, Object> params) {
		StringBuilder sql = new StringBuilder();
		Map<String, Object> args = new HashMap<String, Object>();
		args.putAll(params);
		
		sql.append("WITH dms AS (");
		sql.append(" SELECT ms.uni_id, ms.subject, ms.remark, ms.major_code, ms.majorName, if(LOCATE('-', ms.major_code)<= 6,");
		sql.append(" LEFT(ms.major_code, LOCATE('-', ms.major_code) -1),");
		sql.append(" LEFT(ms.major_code, 6)) AS major_code1,");
		sql.append(" LEFT(ms.major_code, 4) AS major_code2");
		sql.append(" FROM data_major_score ms");
		sql.append(" WHERE ms.year = 2020), dsc AS (");
		sql.append(" SELECT sc.code, sc.name,");
		sql.append(" REPLACE(");
		sql.append(" REPLACE(sc.major_code, 'H',''), 'K', '') AS major_code, sc.majorname, sc.sel1, sc.sel1_code, sc.sel2, sc.sel2_code");
		sql.append(" FROM data_selcourse sc)");
		sql.append(" SELECT u.id, u.name, u.province_label AS province, u.city_name AS city,");
		sql.append(" CASE WHEN u.project985 = 1 THEN '985高校' WHEN u.project211 = 1 THEN '211高校' WHEN u.DFC = 1 THEN '双一流高校' ELSE '' END AS level, dms.major_code, dms.major_code1, mc.realname, dms.majorName, IF(dms.subject = 1, '理科', '文科') as subject, ms.score AS score2019, ms2.score AS score2018, ms3.score AS score2017, if(sc1.sel1 IS NULL, sc2.sel1, sc1.sel1) AS sel1, if(sc1.sel2 IS NULL, sc2.sel2, sc1.sel2) AS sel2");
		sql.append(" FROM dms");
		sql.append(" LEFT JOIN v_university u ON dms.uni_id = u.id");
		sql.append(" LEFT JOIN data_major_score ms ON ms.uni_id = dms.uni_id AND ms.subject = dms.subject AND ms.remark = dms.remark AND ms.major_code = dms.major_code AND ms.year = 2019");
		sql.append(" LEFT JOIN data_major_score ms2 ON ms2.uni_id = dms.uni_id AND ms2.subject = dms.subject AND ms2.remark = dms.remark AND ms2.major_code = dms.major_code AND ms2.year = 2018");
		sql.append(" LEFT JOIN data_major_score ms3 ON ms3.uni_id = dms.uni_id AND ms3.subject = dms.subject AND ms3.remark = dms.remark AND ms3.major_code = dms.major_code AND ms3.year = 2017");
		sql.append(" LEFT JOIN data_major_category mc ON dms.major_code = mc.code");
		sql.append(" LEFT JOIN dsc sc1 ON");
		sql.append(" RIGHT(u.code, 5) = sc1.code AND dms.major_code1 = sc1.major_code");
		sql.append(" LEFT JOIN dsc sc2 ON");
		sql.append(" RIGHT(u.code, 5) = sc2.code AND dms.major_code2 = sc2.major_code");
		sql.append(" WHERE (IF(sc1.sel1_code IS NULL, sc2.sel1_code, sc1.sel1_code) != :sel1_code AND checkSel2(:sel2_code, if(sc1.sel2_code IS NULL, sc2.sel2_code, sc1.sel2_code)) = 1)");
		sql.append(" AND ms.score BETWEEN :startScore AND :endScore ");
		
		List <String> list = new ArrayList<String>();
		// 专业名处理
		if (!"".equals(String.valueOf(args.get("majorName1")))) {
			list.add(String.valueOf(args.get("majorName1")));
			args.remove("majorName1");
		}
		if (!"".equals(String.valueOf(args.get("majorName2")))) {
			list.add(String.valueOf(args.get("majorName2")));
			args.remove("majorName2");
		}
		if (list.size() > 0) {
			sql.append(" AND mc.realname REGEXP :majorName");
			args.put("majorName", StringUtils.join(list.toArray(new String[0]), "|"));
		}

		if(!"".equals(String.valueOf(args.get("name")))){
			sql.append(" AND u.name LIKE :name");
			args.put("name", "%" + args.get("name") + "%");
		}
		if(!"".equals(String.valueOf(args.get("area")))){
			sql.append(" AND CONCAT(u.city_name, '-' ,u.province_label) LIKE :area");
			args.put("area", "%" + args.get("area") + "%");
		}
		sql.append(" GROUP BY dms.uni_id, dms.subject, dms.major_code");
		try {
			NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
			// 编辑特殊的参数
			int score = Integer.parseInt(params.get("score").toString());
			args.remove("score");
			args.put("sel1_code", "his".equals(String.valueOf(args.get("sel1_code"))) ? "phy"
					: "his");
			args.put("startScore", Integer.valueOf(score - 20));
			args.put("endScore", score + 10);
			

			LOGGER.info("SQL语句：" + sql.toString());
			LOGGER.info("参数为：" + args.toString());

			return nameJdbc.queryForList(sql.toString(), args);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	private String getTable() {
		return "career";
	}

}
